package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import Conexao.ConnectionManager;
import Modelo.Aluno;
import Modelo.AlunoSI;
import Modelo.Funcionario;

public class FuncionarioDaoImp implements FuncionarioDao  {
	
	private final String INSERT = "insert into funcionario (nome, matricula, cargo, cpf) values (?,?,?,?)";
	private final String UPDATE = "update funcionario set nome = ?, matricula = ?, cargo = ?, cpf = ? where idfuncionario = ?";
	private final String DELETE = "delete from funcionario where idfuncionario = ?";
	private final String ALL = "select * from funcionario";
	 private final String ID = "select * from Funcionario where idfuncionario = ?";
	 private final String CPF = "select * from Funcionario where cpf = ?";

	private static Connection connection;
	
	public FuncionarioDaoImp() {
		openConnection();
		
	}
	
	public Funcionario salvar(Funcionario e) {
		try {
			PreparedStatement ps = null;

			ps = ConnectionManager.getInstance().getConnection()
					.prepareStatement(INSERT);

			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getMatricula());
			ps.setObject(3, e.getCargo());
			ps.setObject(4, e.getCpf());
			
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}
	
	public void alterar(Funcionario e) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(UPDATE);

			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getMatricula());
			ps.setObject(3, e.getCargo());
			ps.setObject(4, e.getCpf());

			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
	}
	
	public void deletar(Funcionario e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);

			ps.setObject(1, e.getIdFuncionario());

			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
	}

	public List<Funcionario> findAll() {
		List<Funcionario> funcionarios = new ArrayList<Funcionario>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;

			ps = connection.prepareStatement(ALL);

			rs = ps.executeQuery();

			while (rs.next()) {
				Funcionario funcionario = new Funcionario();
				funcionario.setIdFuncionario(rs.getInt("idfuncionario"));
				funcionario.setNome(rs.getString("nome"));
				funcionario.setMatricula(rs.getString("matricula"));
				funcionario.setCargo(rs.getString("cargo"));
				funcionario.setCpf(rs.getString("cpf"));
				
				funcionarios.add(funcionario);
			}

		} catch (SQLException ex) {
			Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			this.closeConnection();
		}
		return funcionarios;
	}
	
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}

	public void openConnection() {
		if (connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}

		if (isConnectionClose()) {
			connection = ConnectionManager.getInstance().getConnection();
		}
	}


	public void closeConnection() {
		if (connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}

	}

	@Override
	public Funcionario buscarPorId(int idFuncionario) {
		List<Funcionario> funcionarios = new ArrayList<Funcionario>();

		try {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ps = connection.prepareStatement(ID);
		ps.setObject(1, idFuncionario);

		rs = ps.executeQuery();

		while (rs.next()) {
			AlunoSI aluno = new AlunoSI();
			Funcionario funcionario = new Funcionario();
			funcionario.setIdFuncionario(rs.getInt("idfuncionario"));
			funcionario.setNome(rs.getString("nome"));
			funcionario.setMatricula(rs.getString("matricula"));
			funcionario.setCargo(rs.getString("cargo"));
			funcionario.setCpf(rs.getString("cpf"));
			
			funcionarios.add(funcionario);
		}

		} catch (SQLException ex) {
		Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
		null, ex);
		} finally {
		this.closeConnection();
		}
		if (funcionarios.isEmpty()) {
		return null;
		} else {
		return funcionarios.get(0);
		}
	}

	@Override
	public Funcionario buscarPorCpf(String cpf) {
		List<Funcionario> funcionarios = new ArrayList<Funcionario>();

		try {
		PreparedStatement ps = null;
		ResultSet rs = null;

		ps = connection.prepareStatement(CPF);
		ps.setObject(1, cpf);

		rs = ps.executeQuery();

		while (rs.next()) {
			AlunoSI aluno = new AlunoSI();
			Funcionario funcionario = new Funcionario();
			funcionario.setIdFuncionario(rs.getInt("idfuncionario"));
			funcionario.setNome(rs.getString("nome"));
			funcionario.setMatricula(rs.getString("matricula"));
			funcionario.setCargo(rs.getString("cargo"));
			funcionario.setCpf(rs.getString("cpf"));
			
			funcionarios.add(funcionario);
		}

		} catch (SQLException ex) {
		Logger.getLogger(FuncionarioDaoImp.class.getName()).log(Level.SEVERE,
		null, ex);
		} finally {
		this.closeConnection();
		}
		if (funcionarios.isEmpty()) {
		return null;
		} else {
		return funcionarios.get(0);
		}
	}

}
